//  Copyright (c) 2010 Mahesh Sharma,Matt MacDonald
//
//  Permission is hereby granted, free of charge, to any person obtaining a copy
//  of this software and associated documentation files (the "Software"), to deal
//  in the Software without restriction, including without limitation the rights
//  to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
//  copies of the Software, and to permit persons to whom the Software is
//  furnished to do so, subject to the following conditions:
//
//  The above copyright notice and this permission notice shall be included in
//  all copies or substantial portions of the Software.
//
//  THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
//  IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
//  FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
//  AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
//  LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
//  OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
//  THE SOFTWARE.

package org.prx.prp.controller;

import java.util.HashMap;

import org.prx.prp.model.Stream;
import org.prx.prp.utility.DatabaseAdapter;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.location.Location;
import android.os.Handler;
import android.os.Message;

public class StreamManager {
	
	
	public static final int DISTANCE_TABLE_CREATED = 53001;
	
	
	private static HashMap<Integer, Stream> getFromDB(Cursor c) {
		HashMap<Integer, Stream> results = null;
		if (c != null) {
			c.moveToFirst();
			if (c.isAfterLast()) {
				c.close();
				return results;
			}
			results = new HashMap<Integer, Stream>(c.getCount());
			do {				
				results.put(c.getInt(c.getColumnIndex("id")),new Stream(c));
			} while (c.moveToNext());
		}
		c.close();
		return results;
	}
	public static double calculateGreatCircleDistance(double latitudeA,double latitudeB,double longitudeA,double longitudeB)
	{
		double R = 3963; 
		return Math.acos(Math.sin(latitudeA/57.2958) * Math.sin(latitudeB/57.2958) + Math.cos(latitudeA/57.2958) * Math.cos(latitudeB/57.2958) * Math.cos(longitudeB/57.2958 - longitudeA/57.2958)) * R;
	}
	public static void calculateDistanceFromCurrentPosition(double currentLatitude,double currentLongitude)
	{
		
		try {			
			DatabaseAdapter.createTable("CREATE TABLE IF NOT EXISTS CURRENT_DISTANCE (SOURCE_ID INTEGER PRIMARY KEY,DISTANCE DOUBLE);");
			DatabaseAdapter.deleteAllRows("CURRENT_DISTANCE");
			Cursor c= DatabaseAdapter.executeQuery("select source_id,latitude,longitude, streams.display_name as name from schedulings join streams on schedulings.stream_id=streams.id join sources on streams.source_id=sources.id group by source_id;",null);
			if (c != null) {
				c.moveToFirst();
				if (c.isAfterLast()) {
					c.close();
					return;
				}
				do {
					 int source_id=c.getInt(c.getColumnIndex("source_id"));
					 double latitude=c.getDouble(c.getColumnIndex("latitude"));
					 double longitude=c.getDouble(c.getColumnIndex("longitude"));
					 double circularDistance=calculateGreatCircleDistance(currentLatitude, latitude, currentLongitude, longitude);
					 
					 ContentValues params=new ContentValues();
					 params.put("source_id",source_id);
					 params.put("distance",circularDistance);
					 DatabaseAdapter.insertRecord("CURRENT_DISTANCE", params);
				} while (c.moveToNext());		
			c.close();
			}
			return;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}	
	}
	public static HashMap<Integer, Stream> findAllStreamsMap()
	{
		Cursor c = DatabaseAdapter
		.executeQuery(
				"SELECT STREAMS.ID AS STREAM_ID, STREAMS.NAME AS STREAM_NAME, STREAMS.DISPLAY_NAME AS STREAM_DISPLAY_NAME, STREAMS.FREQUENCY AS STREAM_FREQUENCY, STREAMS.FORMAT AS STREAM_FORMAT, STREAMS.LOGO_URL AS STREAM_LOGO_URL, STREAMS.AD_SLOT_ID AS STREAMS_AD_SLOT_ID, SOURCES.ID AS SOURCE_ID, SOURCES.STATE_PRIMARY AS SOURCE_STATE_PRIMARY, SOURCES.STATE_SECONDARY AS SOURCE_STATE_SECONDARY, SOURCES.STATE_TERTIARY AS SOURCE_STATE_TERTIARY, SOURCES.LATITUDE AS SOURCE_LATITUDE, SOURCES.LONGITUDE AS SOURCE_LONGITUDE, SOURCES.WEBSITE AS SOURCE_WEBSITE FROM STREAMS JOIN SOURCES ON SOURCES.ID = STREAMS.SOURCE_ID ORDER BY STREAMS.DISPLAY_NAME",
				null);		
		return getFromDB(c);
	}
	public static Cursor findAllStreams() {
		Cursor c = DatabaseAdapter
				.executeQuery(
						"SELECT STREAMS.ID AS STREAM_ID, STREAMS.NAME AS STREAM_NAME, STREAMS.DISPLAY_NAME AS STREAM_DISPLAY_NAME, STREAMS.FREQUENCY AS STREAM_FREQUENCY, STREAMS.FORMAT AS STREAM_FORMAT, STREAMS.LOGO_URL AS STREAM_LOGO_URL, STREAMS.AD_SLOT_ID AS STREAMS_AD_SLOT_ID, SOURCES.ID AS SOURCE_ID, SOURCES.STATE_PRIMARY AS SOURCE_STATE_PRIMARY, SOURCES.STATE_SECONDARY AS SOURCE_STATE_SECONDARY, SOURCES.STATE_TERTIARY AS SOURCE_STATE_TERTIARY, SOURCES.LATITUDE AS SOURCE_LATITUDE, SOURCES.LONGITUDE AS SOURCE_LONGITUDE, SOURCES.WEBSITE AS SOURCE_WEBSITE FROM STREAMS JOIN SOURCES ON SOURCES.ID = STREAMS.SOURCE_ID ORDER BY STREAMS.DISPLAY_NAME",
						null);		
		return c;
	}

	public static Cursor findAllStreamsByList(Integer listId) {
		Cursor c = DatabaseAdapter
				.executeQuery(
						"SELECT STREAMS.ID AS STREAM_ID, STREAMS.NAME AS STREAM_NAME, STREAMS.DISPLAY_NAME AS STREAM_DISPLAY_NAME, STREAMS.FREQUENCY AS STREAM_FREQUENCY, STREAMS.FORMAT AS STREAM_FORMAT, STREAMS.LOGO_URL AS STREAM_LOGO_URL, STREAMS.AD_SLOT_ID AS STREAMS_AD_SLOT_ID, SOURCES.ID AS SOURCE_ID, SOURCES.STATE_PRIMARY AS SOURCE_STATE_PRIMARY, SOURCES.STATE_SECONDARY AS SOURCE_STATE_SECONDARY, SOURCES.STATE_TERTIARY AS SOURCE_STATE_TERTIARY, SOURCES.LATITUDE AS SOURCE_LATITUDE, SOURCES.LONGITUDE AS SOURCE_LONGITUDE, SOURCES.WEBSITE AS SOURCE_WEBSITE FROM STREAMS JOIN SOURCES ON SOURCES.ID = STREAMS.SOURCE_ID JOIN STREAM_LISTINGS ON STREAM_LISTINGS.STREAM_LIST_ID = ? AND STREAM_LISTINGS.STREAM_ID = STREAMS.ID ORDER BY STREAMS.ID",
						new String[] { listId.toString() });
		return c;
	}

	public static HashMap<Integer, Stream> findAllByCategory(Integer categoryId) {
		Cursor c = DatabaseAdapter
				.executeQuery(
						"SELECT STREAMS.ID AS STREAM_ID, STREAMS.TITLE AS STREAM_TITLE, STREAMS.CATEGORY AS STREAM_CATEGORY, STREAMS.DESCRIPTION AS STREAM_DESCRIPTION, STREAMS.IMAGE_URL AS STREAM_IMAGE_URL, STREAMS.WEBSITE AS STREAM_WEBSITE, STREAMS.PODCAST AS STREAM_PODCAST FROM STREAMS JOIN CATEGORIZATIONS ON CATEGORIZATIONS.STREAM_ID = STREAMS.ID AND CATEGORIZATIONS.CATEGORY_ID = ? WHERE STREAMS.PODCAST IS NOT NULL AND STREAMS.PODCAST != '' ORDER BY STREAMS.TITLE",
						new String[] { categoryId.toString() });
		return getFromDB(c);
	}

	public static Cursor findAllFavorites() {
		Cursor c = DatabaseAdapter
				.executeQuery("SELECT STREAM_ID,PROGRAMS.TITLE AS PROGRAM_TITLE,SUBSTR(TIME(START_TIME,'localtime'),0,5)||' TO '||SUBSTR(TIME(END_TIME,'localtime'),0,5) AS TIME,STREAMS.DISPLAY_NAME AS STREAM_DISPLAY_NAME,SCHEDULINGS.ID AS _id FROM SCHEDULINGS JOIN FAVORITE_STATIONS ON SCHEDULINGS.STREAM_ID=FAVORITE_STATIONS.ID JOIN STREAMS ON SCHEDULINGS.STREAM_ID=STREAMS.ID JOIN PROGRAMS ON SCHEDULINGS.PROGRAM_ID=PROGRAMS.ID",null);
		return c;
	}

	public static Stream findById(Integer StreamId) {
		/**Please correct this part by changing all create to integer**/
		Cursor c = DatabaseAdapter
				.executeQuery(
						"SELECT STREAMS.ID AS STREAM_ID, STREAMS.NAME AS STREAM_NAME, STREAMS.DISPLAY_NAME AS STREAM_DISPLAY_NAME, STREAMS.FREQUENCY AS STREAM_FREQUENCY, STREAMS.FORMAT AS STREAM_FORMAT, STREAMS.LOGO_URL AS STREAM_LOGO_URL, STREAMS.AD_SLOT_ID AS STREAMS_AD_SLOT_ID, SOURCES.ID AS SOURCE_ID, SOURCES.STATE_PRIMARY AS SOURCE_STATE_PRIMARY, SOURCES.STATE_SECONDARY AS SOURCE_STATE_SECONDARY, SOURCES.STATE_TERTIARY AS SOURCE_STATE_TERTIARY, SOURCES.LATITUDE AS SOURCE_LATITUDE, SOURCES.LONGITUDE AS SOURCE_LONGITUDE, SOURCES.WEBSITE AS SOURCE_WEBSITE FROM STREAMS JOIN SOURCES ON SOURCES.ID = STREAMS.SOURCE_ID WHERE STREAMS.ID = '"+StreamId+"'",null);
		c.moveToFirst();
		//Log.d("PRPAND","Count is"+c.getCount());
		Stream p = new Stream(c);
		c.close();
		return p;
	}

	public static void addFavorite(Integer StreamId) {
		Cursor c = DatabaseAdapter.executeQuery("SELECT IFNULL(MAX(POSITION), -1) AS LAST_INDEX FROM FAVORITE_STATIONS",null);
		c.moveToFirst();
		int new_position=c.getInt(c.getColumnIndex("LAST_INDEX"))+1;
		c.close();
		ContentValues p=new ContentValues();
		p.put("ID",StreamId);
		p.put("POSITION",new_position);
		DatabaseAdapter.insertRecord("FAVORITE_STATIONS",p);
		Cursor c1 = DatabaseAdapter.executeQuery("SELECT * FROM FAVORITE_STATIONS",null);
		c1.moveToFirst();
		//Log.d("PRPAND","Count is"+c1.getCount());
		c1.close();
	}

	public static void removeFavorite(Integer StreamId) {
		DatabaseAdapter.deleteRecord("FAVORITE_STATIONS", new String[]{"ID"},new String[] { StreamId.toString() });		
	}

	public static boolean isFavorite(Integer StreamId) {

		Cursor c = DatabaseAdapter
				.executeQuery(
						"SELECT * FROM FAVORITE_STATIONS WHERE FAVORITE_STATIONS.ID=?",new String[]{StreamId.toString()});
		c.moveToFirst();
		int count=c.getCount();		
		boolean result = (count > 0 ? true : false);
		c.close();
		return result;
	}

	public static void moveFavorite(Stream Stream, Integer fromPosition,
			Integer toPosition) {
		if (fromPosition > toPosition) {
			Cursor c = DatabaseAdapter
					.executeQuery(
							"update favorite_stations set position = (position + 1) where position < ? and position >= ?",
							new String[] { fromPosition.toString(),
									toPosition.toString() });
			c.close();

		} else if (fromPosition < toPosition) {
			Cursor c = DatabaseAdapter
					.executeQuery(
							"update favorite_stations set position = (position - 1) where position > ? and position <= ?",
							new String[] { fromPosition.toString(),
									toPosition.toString() });
			c.close();
		}
		Cursor c = DatabaseAdapter.executeQuery(
				"update favorite_stations set position = ? where id = ?",
				new String[] { toPosition.toString(),
						Stream.getStreamId().toString() });
		c.close();
	}
	public static void initDistanceTable(final Location location, final Handler handler) {
		
		new Thread(new Runnable(){
			@Override
			public void run() {
				calculateDistanceFromCurrentPosition(location.getLatitude(), location.getLongitude());
				Message.obtain(handler, DISTANCE_TABLE_CREATED).sendToTarget();
			}
		}).start();
	}

}
